/*
 * StoreProductDAO.java
 *
 *  
 */

package finalintex2web;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author Prabol Bhandari
 */
public class StoreProductDAO {
    
    //singleton pattern
    private static StoreProductDAO instance = null;
    
    /** Creates a new instance of StoreProductDAO */
    public StoreProductDAO() {
    }
    
    /**
     * enforcement of the singleton pattern
     * @return the instance which will be used when the DAO is used
     */
    public static synchronized StoreProductDAO getInstance() {
        if (instance == null) {
            instance = new StoreProductDAO();
        }
        return instance;
    }
    
    
    //////////////////////////
    //CREATE
    /**
     * This method is used to create a business object of type StoreProduct.
     * @return StoreProductBO
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors in obtaining the GUID
     */
    public StoreProductBO create() throws DataException {
        String id = null;
        try{
            id = GUID.generate();
        } catch (Exception e) {
            throw new DataException("Could not generate new StoreProduct", e);
        }
        StoreProductBO sp = new StoreProductBO(id);
        Cache c = Cache.getInstance();
        c.put(sp.getSpguid(), sp);
        return sp;
    }
    
    //////////////////////////
    //READ
    /**
     * This method reads the StoreProduct object from the guid.
     * @param id the GUID for a particular StoreProduct to be read
     * @return StoreProductBO with the matching id
     * @throws edu.byu.isys413.estrogen.data.DataException if there is an error with the SQL or the connection
     */
    public synchronized StoreProductBO read(String id) throws DataException, Exception {
        
        StoreProductBO sp = (StoreProductBO)Cache.getInstance().get(id);
        
        if (sp == null) {
            Connection conn = null;
            try {
                conn = ConnectionPool.getInstance().get();
                
                sp = read(id, conn);
                
                conn.commit();
                ConnectionPool.getInstance().release(conn);
            } catch (DataException e) {
                throw new DataException("Could not get a connection to the database.");
            } catch (SQLException e) {
                //rollback
                try {
                    conn.rollback();
                    ConnectionPool.getInstance().release(conn);
                } catch (SQLException e2) {
                    throw new DataException("ERROR: Could not even release the connection", e2);
                }
                throw new DataException("Could not retrieve record for id=" + id, e);
            }
        }
        
        return sp;
    }
    
    /**
     * This method reads the StoreProduct object with the id and a connection
     * @param id the GUID for a particular StoreProduct to be read
     * @param conn the connection to use for the reading
     * @return StoreProductBO with the specified id
     * @throws edu.byu.isys413.estrogen.data.DataException if there is any error in the reading
     * @throws java.sql.SQLException if there is any error in the SQL
     */
    public synchronized StoreProductBO read(String id, Connection conn) throws DataException, SQLException {                  //will also create a BO, but it's pulling it off the database and creating the object to be used
        StoreProductBO sp = (StoreProductBO)Cache.getInstance().get(id);
        
        if (sp == null) {
            
            try {
                PreparedStatement SELECT = conn.prepareStatement("SELECT * FROM storeproduct WHERE spguid = ?");
                SELECT.setString(1, id);
                ResultSet rs = SELECT.executeQuery();
                
                conn.commit();
                
                if (rs.next())
                {
                    sp = new StoreProductBO(id);
                    sp.setQtyonhand(rs.getInt("quantityonhand"));
                    sp.setShelflocation(rs.getString("shelflocation"));
                    sp.setReorderpt(rs.getInt("reorderpoint"));
                    sp.setQtytoorder(rs.getInt("quantitytoorder"));
                    sp.setQtyonorder(rs.getInt("qtyonorder"));
                    sp.setConceptualprod((ConceptualProductBO)ConceptualProductDAO.getInstance().read(rs.getString("conceptualid")));
                    sp.setStore(StoreDAO.getInstance().read(rs.getString("storeguid")));
                    
                    Cache.getInstance().put(id, sp);
                    
                    sp.setAlreadyindb(true);
                } else {
                    throw new DataException("Object was not found in the database.");
                }
                
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve record for id=" + id, e);
            }catch (Exception e) {
                throw new DataException("error here...", e);
            }
        }
        
        return sp;
    }
    
    /**
     * This method gets the connection and puts the StoreProduct into the database.
     * @param sp the StoreProductBO to be saved
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors while processing
     * @throws java.sql.SQLException if there are any errors executing the SQL
     */
    public synchronized void save(StoreProductBO sp) throws SQLException, DataException, Exception {
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            System.out.println("Going to save with conn");
            save(sp, conn);
            
            conn.commit();
            ConnectionPool.getInstance().release(conn);
        } 
        catch (DataException e) 
        {
            conn.rollback();
            throw new DataException("Could not connect to the database.", e);
        }
        
    }
    
    /**
     * This method puts the StoreProduct into the database, either inserting
     * or updating depending on whether it exists in the database already.
     * @param sp the StoreProductBO to be saved
     * @param conn the connection to be used
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors while processing
     * @throws java.sql.SQLException if there are any errors executing the SQL
     */
    synchronized void save(StoreProductBO sp, Connection conn) throws SQLException, DataException, Exception
    {
        if (sp.isAlreadyindb())
        {
            System.out.println("updating...");
            update(sp, conn);
        } 
        else 
        {
            System.out.println("inserting...");
            System.out.println("id for the store product is "+sp.getSpguid());
            insert(sp, conn);
            
        }
        sp.setDirty(false);
    }
    
    /**
     * This method saves the updated StoreProduct to the database if it already exists.
     * @param sp the StoreProductBO to be saved
     * @param conn the connection passed from the save method
     * @throws java.sql.SQLException if there are any problems executing the SQL
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any other errors while executing the update
     */
    void update(StoreProductBO sp, Connection conn) throws SQLException, DataException, Exception {
        try {
            System.out.println("Store product GUID:" + sp.getSpguid());
            PreparedStatement UPDATE = conn.prepareStatement("UPDATE storeproduct SET quantityonhand = ?, shelflocation = ?, reorderpoint = ?, quantitytoorder = ?, quantityonorder = ?, conceptualid = ?, storeid = ? WHERE storeproductid = ?");
            UPDATE.setInt(1, sp.getQtyonhand());
            UPDATE.setString(2, sp.getShelflocation());
            UPDATE.setInt(3, sp.getReorderpt());
            UPDATE.setInt(4, sp.getQtytoorder());
            UPDATE.setInt(5, sp.getQtyonorder());
            UPDATE.setString(6, sp.getConceptualprod().getId());
            UPDATE.setString(7, sp.getStore().getId());
            UPDATE.setString(8, sp.getSpguid());
            
            UPDATE.executeUpdate();
            System.out.println("Update executed");
            UPDATE.close();
             ConnectionPool.getInstance().release(conn);
        } catch (SQLException e) {
            conn.rollback();
            throw new DataException("Could not perform the update on the StoreProduct", e);
        }
    }
    
    /**
     * This method saves the StoreProduct into the database if it did not previously
     * exist.
     * @param sp the StoreProductBO to be saved
     * @param conn the connection passed from the save method
     * @throws java.sql.SQLException if there are any problems executing the SQL
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any other errors while executing the insert
     */
    public void insert(StoreProductBO sp, Connection conn) throws SQLException, DataException {
        try 
        {
            System.out.println("Insert of store product");
            //PreparedStatement INSERT = conn.prepareStatement("INSERT into storeproduct VALUES (?, ?, ?, ?, ?, ?, ?, ?)" );
PreparedStatement INSERT = conn.prepareStatement("INSERT into storeproduct VALUES (?,?,?,?,?,?,?,?)");
            INSERT.setString(1, sp.getSpguid());
            //System.out.println(sp.getSpguid());
            INSERT.setString(2, sp.getConceptualprod().getId());
            System.out.println(sp.getConceptualprod().getId());
            INSERT.setString(3, sp.getStore().getId());
            System.out.println(sp.getStore().getId());
            INSERT.setInt(4, sp.getQtyonhand());
            System.out.println(sp.getQtyonhand());
            INSERT.setString(5, sp.getShelflocation());
            System.out.println(sp.getShelflocation());
            INSERT.setInt(6, sp.getReorderpt());
            System.out.println(sp.getReorderpt());
            INSERT.setInt(7, sp.getQtytoorder());
            System.out.println(sp.getQtytoorder());
            INSERT.setInt(8, sp.getQtyonorder());
            System.out.println(sp.getQtyonorder());
            INSERT.executeUpdate();
            INSERT.close();
            
            sp.setAlreadyindb(true);
            
        } catch (SQLException e) {
            conn.rollback();
            throw new DataException("Could not insert the StoreProduct into the database", e);
        }
    }
    
    
    /////////////////////////////////DELETE//////////////////////////////////////////////////
    //A delete method was intentionally omitted here for business reasons--we would like
    //to keep a record of all StoreProducts Because they are attached to vendors, products and other essential elements
    //that should not be deleted from the database.  Rather than deleting them completely,
    //we might instead build in a "deleted" boolean variable where we can flag a customer if they are
    //no longer active.
    
    ////////////////////////////////////////////////////////////////////////
    ///  SEARCH methods
    
    public List<StoreProductBO> getAll() throws DataException, SQLException, Exception {
        // get the connection
        Connection conn = ConnectionPool.getInstance().get();
        List<StoreProductBO> list = new LinkedList();
        
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM APP.storeproduct");
        ResultSet rs = stmt.executeQuery();
        
        // while loop to populate the list from the results
        while (rs.next()) {
            
           StoreProductBO storeprod = StoreProductDAO.getInstance().create();
            
            storeprod.setQtyonhand(rs.getInt("quantityonhand"));
            storeprod.setShelflocation(rs.getString("shelflocation"));
            storeprod.setReorderpt(rs.getInt("reorderpoint"));
            storeprod.setQtytoorder(rs.getInt("quantitytoorder"));
            storeprod.setQtyonorder(rs.getInt("quantityonorder"));
            storeprod.setConceptualprod((ConceptualProductBO)ConceptualProductDAO.getInstance().read(rs.getString("conceptualid")));
            storeprod.setStore(StoreDAO.getInstance().read(rs.getString("storeid")));
            
            list.add(storeprod);
        }
        
        // release my connection
        ConnectionPool.getInstance().release(conn);
        // return the list
        return list;
    }
    
    public List<StoreProductBO> getAlltoOrder() throws DataException, SQLException , Exception
    {
        // get the connection
        Connection conn = ConnectionPool.getInstance().get();
        List<StoreProductBO> list = new LinkedList();
        
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM storeproduct WHERE (((quantityonhand + quantityonorder) < reorderpoint) AND (storeid = storeproductid))");
        ResultSet rs = stmt.executeQuery();
        
        // while loop to populate the list from the results
        while (rs.next()) 
        {
            
            StoreProductBO storeprod = StoreProductDAO.getInstance().create();
            storeprod.setQtyonhand(rs.getInt("quantityonhand"));
            storeprod.setShelflocation(rs.getString("shelflocation"));
            storeprod.setReorderpt(rs.getInt("reorderpoint"));
            storeprod.setQtytoorder(rs.getInt("quantitytoorder"));
            storeprod.setQtyonorder(rs.getInt("quantityonorder"));
            storeprod.setConceptualprod((ConceptualProductBO)ConceptualProductDAO.getInstance().read(rs.getString("conceptualid")));
            storeprod.setStore(StoreDAO.getInstance().read(rs.getString("storeid")));
            list.add(storeprod);
        }
        
        // release my connection
        ConnectionPool.getInstance().release(conn);
        // return the list
        return list;
    }
    
}